/*
 * memoDb.cpp
 *
 *  Created on: 19-05-2013
 *      Author: Kopik
 */

#include "memoDb.h"
#include <QDir>
#include <QIODevice>
#include <QtSql/QtSql>
#include <bb/data/SqlConnection>
#include <bb/data/SqlDataAccess>

#include <bb/system/SystemDialog>

using namespace bb::system;
using namespace bb::data;

MemoDb::MemoDb()
	: m_initialized(false)
{
	m_initialized = initializeDatabase();
	m_categoryModel = new GroupDataModel(this);
	m_categoryModel->setSortingKeys(QStringList());
	m_categoryModel->setGrouping(ItemGrouping::None);
}

bool MemoDb::initializeDatabase() {

    QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE", "memo-data");
    database.setDatabaseName("./data/memo.db");

    if (!database.open()) {
        const QSqlError error = database.lastError();
        alert(tr("Error opening connection to the database: %1").arg(error.text()));
        qDebug() << "\nDatabase NOT opened.";
        return false;
    }

    // database file already exists
	if (database.tables().contains("categories"))
		return true;

    QSqlQuery query(database);
    if (query.exec("CREATE TABLE categories( id INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			" name VARCHAR(50), "
	                "desc VARCHAR(255));")) {
        qDebug() << "Categories table created.";
    } else {
        const QSqlError error = database.lastError();
        alert(tr("could not create categories table: %1").arg(error.text()));
        qDebug() << "\nDatabase NOT opened.";
        return false;
    }

    if (query.exec("CREATE TABLE sets( id INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			"category_id INTEGER, "
	    			" name VARCHAR(50), "
	                "desc VARCHAR(255),"
	    			"FOREIGN KEY (category_id) REFERENCES categories(id));")) {
        qDebug() << "Set table created.";
    } else {
        const QSqlError error = database.lastError();
        alert(tr("could not create set table: %1").arg(error.text()));
        qDebug() << "\nDatabase NOT opened.";
        return false;
    }

    if (query.exec("CREATE TABLE questions( id INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			"set_id INTEGER, "
	    			" text VARCHAR(255), "
	    			"FOREIGN KEY (set_id) REFERENCES sets(id));")) {
        qDebug() << "question table created.";
    } else {
        const QSqlError error = database.lastError();
        alert(tr("could not create question table: %1").arg(error.text()));
        qDebug() << "\nDatabase NOT opened.";
        return false;
    }

    if (query.exec("CREATE TABLE answers( id INTEGER PRIMARY KEY AUTOINCREMENT, "
	    			"question_id INTEGER, "
	    			" text VARCHAR(255), "
    				" is_valid BOOL, "
	    			"FOREIGN KEY (question_id) REFERENCES questions(id));")) {
        qDebug() << "question table created.";
    } else {
        const QSqlError error = database.lastError();
        alert(tr("could not create question table: %1").arg(error.text()));
        qDebug() << "\nDatabase NOT opened.";
        return false;
    }

    return true;
}

QVariantList MemoDb::getCategoriesModelItems() {

	QDir dir;
	SqlDataAccess sda(dir.absoluteFilePath("./data/memo.db"));

	QVariant list = sda.execute("select c.name as category, c.desc as categoryDesc, "
			"	s.name as infoText, s.desc as desc from categories c left outer join sets s"
			" 	on c.id = s.category_id order by c.name, s.name;");

	return list.value<QVariantList>();
}

QList<QSharedPointer<Category> > MemoDb::getCategories(){


}


QList<QSharedPointer<Question> > MemoDb::getQuestions(const QString &setName) {

	QList<QSharedPointer<Question> > result;

	const QString sql = "select * from questions q inner join answers a "
						"	in q.id = a.question_id where q.name = :name "
						"	order by q.id ";

	QSqlQuery query(QSqlDatabase::database());
	query.prepare(sql);
	query.bindValue(":name", setName);

	if (!query.exec()) {
        const QSqlError error = query.lastError();
        alert(tr("could not get questions: %1").arg(error.text()));
        qDebug() << "sql error\n." << error.text();
        return result;
	}

	int questionId = 0;
	QString text;
	QList<QSharedPointer<Answer> > answers;
	while (query.next()) {
		int newQuestionId = query.value(0).toInt();
		if (questionId != 0 && newQuestionId != questionId) {
			QSharedPointer<Question> question = QSharedPointer<Question>(new Question(questionId, text, answers));
			result.append(question);
			answers.clear();
			continue;
		}

		questionId = newQuestionId;
		text = query.value(2).toString();

		int answerId = query.value(3).toInt();
		QString answerTxt = query.value(5).toString();
		bool isValid = query.value(6).toBool();

		QSharedPointer<Answer> answer = QSharedPointer<Answer>(new Answer(answerId, answerTxt, isValid));
		answers.append(answer);
	}

	return result;
}

void MemoDb::saveSet(const Set& set, const int categoryId) {
	QSqlDatabase db = QSqlDatabase::database("memo-data");
	QSqlQuery query(db);
	const QString sql = "insert into sets(name, desc, category_id)"
							"values(:name, :desc, :category_id)";
	query.prepare(sql);
	query.bindValue(":name", set.name());
	query.bindValue(":desc", set.desc());
	query.bindValue(":category_id", categoryId);

	if (!query.exec()) {
		const QSqlError error = query.lastError();
		qDebug() << "Sets sql error\n." << error.text();
		return;
	}
	int set_id = query.lastInsertId().toInt();
	QList<QSharedPointer<Question> > questions = set.questions();
	QList<QSharedPointer<Question> >::iterator it;
	const QString q_sql = "insert into questions(text, set_id)"
							"values(:text, :set_id)";
	const QString a_sql = "insert into answers(text, valid, question_id)"
							"values(:text, :valid, :question_id)";
	for(it = questions.begin();it != questions.end(); ++it)
	{
		QSharedPointer<Question> q = *it;

		QSqlQuery query(db);
		query.prepare(q_sql);
		query.bindValue(":text", q->text());
		query.bindValue(":set_id", set_id);

		if (!query.exec()) {
			const QSqlError error = query.lastError();
			qDebug() << "Question sql error\n." << error.text();
			return;
		}
		int question_id = query.lastInsertId().toInt();
		QList<QSharedPointer<Answer> > answers = q->answers();
		QList<QSharedPointer<Answer> >::iterator ait;

		for(ait = answers.begin();ait != answers.end();++ait)
		{
			QSharedPointer<Answer> a = *ait;

			QSqlQuery query(db);
			query.prepare(a_sql);
			query.bindValue(":text", a->text());
			query.bindValue(":valid", a->valid());
			query.bindValue(":question_id", question_id);

			if (!query.exec()) {
				const QSqlError error = query.lastError();
				qDebug() << "Answers sql error\n." << error.text();
				return;
			}
		}
	}

	updateCategoryModel();

}

void MemoDb::alert(const QString &message)
{
    SystemDialog *dialog; // SystemDialog uses the BB10 native dialog.
    dialog = new SystemDialog(tr("OK"), 0); // New dialog with on 'Ok' button, no 'Cancel' button
    dialog->setTitle(tr("Alert")); // set a title for the message
    dialog->setBody(message); // set the message itself
    dialog->setDismissAutomatically(true); // Hides the dialog when a button is pressed.

    // Setup slot to mark the dialog for deletion in the next event loop after the dialog has been accepted.
    connect(dialog, SIGNAL(finished(bb::system::SystemUiResult::Type)), dialog, SLOT(deleteLater()));
    dialog->show();
}

void MemoDb::addCategory(const QString name, const QString desc) {
	const QString sql = "insert into categories(name, desc)"
						"values(:name, :desc)";
	QSqlQuery query(QSqlDatabase::database("memo-data"));
	query.prepare(sql);
	query.bindValue(":name", name);
	query.bindValue(":desc", desc);

	if (!query.exec()) {
        const QSqlError error = query.lastError();
        qDebug() << "sql error\n." << error.text();
	}
	this->updateCategoryModel();
}
void MemoDb::updateCategoryModel()
{
	const QString sql = "select c.id, c.name, c.desc, s.id, s.name, s.desc from categories c left outer join sets s "
								"on c.id = s.category_id "
								"order by c.id, s.id ";

	QSqlQuery query(QSqlDatabase::database("memo-data"));

	if (!query.exec(sql)) {
		const QSqlError error = query.lastError();
		qDebug() << "sql error\n." << error.text();
	}
	m_categoryModel->clear();
	int setId = -1;

	while (query.next()) {
		int newSetId = query.value(0).toInt();
		if (newSetId != setId) {
			Category* cat = new Category(query.value(0).toInt(), query.value(1).toString(), query.value(2).toString());
			m_categoryModel->insert(cat);
		}

		setId = newSetId;
		if(!query.value(3).isNull()){
			QList<QSharedPointer<Question> > questions;
					Set* set = new Set(query.value(3).toInt(), query.value(4).toString(), query.value(5).toString(), questions);
					m_categoryModel->insert(set);
		}
	}
}

bb::cascades::GroupDataModel* MemoDb::categoryModel() {
	updateCategoryModel();
	return m_categoryModel;
}

